package com.gongguizhijia.CreateAccessExcel;

import com.gongguizhijia.Util.CountColumnUtil;
import com.gongguizhijia.Util.ExcelWriterUtil;
import com.gongguizhijia.pojo.*;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import static com.gongguizhijia.Util.CountColumnUtil.getFileName;

/**
 * @author Chenglei
 * @date 2021/6/16 14:39
 */
public class GetDataFromSource {


    public static void main(String[] args) throws Exception {

        getDataFromSource("DataFlow", DataFlow.class);
        getDataFromSource("DTC", DTC.class);
        getDataFromSource("ECU", ECU.class);
        getDataFromSource("EcuDataFlowConfig", EcuDataFlowConfig.class);
        getDataFromSource("EcuInfoParseConfig", EcuInfoParseConfig.class);

    }


    /**
     * 读取mdb文件 ，生成 对应 Excel数据表
     *
     * @param excelName
     * @param clazz
     * @param <U>
     * @throws Exception
     */
    public static <U> void getDataFromSource(String excelName, Class<U> clazz) throws Exception {

        //获取表格所有标题
        Set<String> setColumnName = CountColumnUtil.getColumnName(excelName);
        //ACCESS数据库驱动连接
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        //mdb文件夹路径
        String desktopPath = "D:/jjf";
        //获取文件夹下 所有文件名
        List<String> fileList = getFileName();
        String mdbPath = null;
        List<U> modelsAll = new ArrayList<>();
        //遍历文件
        for (String fileName : fileList) {
            mdbPath = fileName;
            //连接数据库
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://" + desktopPath + "//" + mdbPath);
            DatabaseMetaData metaData1 = conn.getMetaData();
            //获取该数据库下 所有表名称
            ResultSet tables = metaData1.getTables(null, null, "%", null);
            while (tables.next()) {
                //表名
                String tablesString = tables.getString(3);
                if (excelName.equals(tablesString)) {
                    Statement stmt = conn.createStatement();
                    //查询表数据
                    String sql = "SELECT * FROM " + excelName;
                    ResultSet rs = stmt.executeQuery(sql);
                    ResultSetMetaData metaData = rs.getMetaData();
                    int count = metaData.getColumnCount();
                    List<U> models = new ArrayList<>();
                    while (rs.next()) {
                        //创建实体对象
                        Class<U> c = clazz;
                        U u = c.newInstance();

                        for (int i = 1; i <= count; i++) {
                            //给fileName属性设置值
                            try {
                                Field field = clazz.getDeclaredField("fileName");
                                //setAccessible这行代码把对象上的fileName字段设置为可访问
                                field.setAccessible(true);
                                // 设置该u的fileName属性
                                field.set(u, fileName);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            String columnName = metaData.getColumnName(i);
                            for (String setForColumnName : setColumnName) {
                                if (setForColumnName.equals(columnName)) {
                                    String result = rs.getString(setForColumnName);
                                    // 获取实体类的所有属性，返回Field数组
                                    Field[] field = u.getClass().getDeclaredFields();
                                    // 遍历所有属性
                                    for (int j = 0; j < field.length; j++) {
                                        // 获取属性的名字
                                        String name = field[j].getName();
                                        if (setForColumnName.equalsIgnoreCase(name)) {
                                            // 将属性的首字符大写，方便构造get，set方法
                                            name = name.substring(0, 1).toUpperCase() + name.substring(1);
                                            // 获取属性的类型
                                            String type = field[j].getGenericType().toString();
                                            // 如果type是类类型，则前面包含"class "，后面跟类名
                                            if (type.equals("class java.lang.String")) {
                                                Method m = u.getClass().getMethod("get" + name);
                                                // 调用getter方法获取属性值
                                                String value = (String) m.invoke(u);
                                                if (value == null) {
                                                    m = u.getClass().getMethod("set" + name, String.class);
                                                    m.invoke(u, result);
                                                }
                                            }
                                        }
                                    }
                                }

                            }

                        }
                        models.add(u);
                    }
                    modelsAll.addAll(models);

                    rs.close();
                    stmt.close();
                    conn.close();
                }
            }

            String sheetName = excelName;
            String filePath = "D:// " + excelName + ".xlsx";
            //生成新的Excel
            ExcelWriterUtil.getNewExcelFile(sheetName, filePath, clazz, modelsAll);
        }
    }
}
